let { jwt, jwtKey } = require('../server_jwt');
let express = require('express');
let router = express.Router();
let db = require('../db');
let xss = require('xss')  // 导入xss模块为了防止用户的脚本攻击

// 点击一个帖子
router.post('/one', (req, res) => {
    const token = req.headers['authorization'];
    jwt.verify(token, jwtKey, (err, decoded) => {
        if (err) {
            return res.send({msg: "登录验证失败，请重新登录", login: false});
        } else if (parseInt(+new Date() / 1000) >= decoded.exp) {
            return res.send({msg: "登录超时:", login: false});
        }

        if (!req.body.postID) {
            return res.send({msg: "请求帖子id为空", login: false});
        }

        // sendResult用来存储返回到客户端的数据
        let sendResult = {};
        let sql_show_post = 'select u.user_icon, u.username, p.intor, p.imgList, p.postID, p.createTime from post p join users u where p.uid=u.uid and postID=?';
        let postID = req.body.postID;

        // 第一个查询请求(返回帖子)
        db.query(sql_show_post, postID, (err, result) => {
            if (err) {
                console.log(err.message);
                return res.status(500).send('Server Error');
            }
            sendResult['post'] = result;
            let sql_show_comment = 'select u.user_icon, c.createTime, c.content, u.username from commend c join users u where c.uid=u.uid and postID=?';
            
            // 第二个查询请求(返回帖子的评论)
            db.query(sql_show_comment, postID, (err, result) => {
                if (err) {
                    console.log(err.message);
                    return res.status(500).send('Server Error');
                }
                sendResult['commend'] = result;
                res.status(200).json(sendResult);
            });
        });
    });
});

// 发表评论
router.post('/sendCommend', (req, res) => {
    const token = req.headers['authorization'];
    jwt.verify(token, jwtKey, (err, decoded) => {
        if (err) {
            return res.send({ msg: "登录验证失败，请重新登录", login: false });
        } else if (parseInt(+new Date() / 1000) >= decoded.exp) {
            return res.send({ msg: "登录超时:", login: false });
        }

        let content = xss(req.body.content);  // 因为用户可以输入内容所以这里检查一下
        let sql_insert_commend = 'insert into commend(postID, content, uid) values (?, ?, ?)';
        db.query(sql_insert_commend, [req.body.postID, content, decoded.uid], (err) => {
            if (err) {
                return console.log('数据库处理的时候出错了:', err.message);
            }
            return res.status(200).send("");
        });
    });
});

// 展示所有帖子
router.get('/all', (req, res) => {
    const token = req.headers['authorization'];
    jwt.verify(token, jwtKey, (err, decoded) => {
        if (err) {
            return res.send({ msg: "登录验证失败，请重新登录", login: false });
        } else if (parseInt(+new Date() / 1000) >= decoded.exp) {
            return res.send({ msg: "登录超时:", login: false });
        }

        // 获取请求中的offset和limit参数，同时设置默认值
        const limit = parseInt(req.query.limit, 10) || 10; // 默认每次返回10个帖子
        const offset = parseInt(req.query.offset, 10) || 0; // 默认从第0个开始
        console.log(limit, offset);
        // 使用参数化查询避免SQL注入
        const sql_show_all = `
            SELECT
                u.user_icon,
                u.username,
                p.createTime,
                COALESCE(up.upvote_count, 0) AS upvotes,
                COALESCE(c.comment_count, 0) AS comments,
                p.imgList,
                p.intor,
                p.postID,
                IF(uv.uid IS NOT NULL, 1, 0) AS user_upvoted
            FROM
                post p
            INNER JOIN users u ON p.uid = u.uid
            LEFT JOIN (
                SELECT postID, COUNT(*) as upvote_count
                FROM upvote
                GROUP BY postID
            ) up ON p.postID = up.postID
            LEFT JOIN (
                SELECT postID, COUNT(*) as comment_count
                FROM commend
                GROUP BY postID
            ) c ON p.postID = c.postID
            LEFT JOIN upvote uv ON p.postID = uv.postID AND uv.uid = ?
            ORDER BY p.createTime DESC
            LIMIT ? OFFSET ?;
        `;
        
        const queryParams = [decoded.uid, limit, offset];
        
        db.query(sql_show_all, queryParams, (err, result) => {
            if (err) {
                res.status(500).send('Server Error');
            } else {
                res.status(200).json(result);
            }
        });
    });
});


// 发帖
router.post('/create', (req, res) => {
    const token = req.headers['authorization'];
    jwt.verify(token, jwtKey, (err, decoded) => {
        if (err) {
            res.send({msg: "登录验证失败，请重新登录", login: false});
        } else if (parseInt(+new Date() / 1000) >= decoded.exp) {
            res.send({msg: "登录超时:", login: false});
        }
        
        let intor = xss(req.body.intor);
        let sql_post = 'insert into post(uid, typeID, intor, imgList) values (?, 1, ?, ?)';
        let data = [decoded.uid, intor, req.body.imgList];

        // 图片和内容都为空的话就返回
        if (data[1] == data[2]) {
            // res.send(406);
            res.status(406).send("");
            return;
        }

        db.query(sql_post, data, (err) => {
            if (err) {
                console.log(err.message);
            }
            // res.send(200);
            res.status(200).send("");
        })
    });
});

// 点赞与取消点赞
router.post('/upvote', (req, res) => {
    const token = req.headers['authorization'];
    jwt.verify(token, jwtKey, (err, decoded) => {
        if (err) {
            res.send({ msg: "登录验证失败，请重新登录", login: false });
        } else if (parseInt(+new Date() / 1000) >= decoded.exp) {
            res.send({ msg: "登录超时:", login: false });
        }

        sql_insert = 'INSERT INTO upvote (uid, postID) VALUES (?, ?)';
        db.query(sql_insert, [decoded.uid, req.body.postID], (err) => {
            // 如果插入出错，那就是要删除
            if (err) {
                sql_delete = 'DELETE FROM upvote WHERE uid = ? AND postID = ?';
                db.query(sql_delete, [decoded.uid, req.body.postID], (err) => {
                    if (err) {
                        // 如果删除操作也失败，发送500错误
                        return res.status(500).send("取消点赞失败");
                    }
                });
            }
            // 返回点赞数量
            sql_select = `SELECT p.postID, COALESCE(COUNT(DISTINCT up.uid), 0) AS UpvoteCount
                          FROM post p
                          LEFT JOIN upvote up ON p.postID = up.postID
                          WHERE p.postID = ?
                          GROUP BY p.postID;`;
            db.query(sql_select, req.body.postID, (err, result) => {
                if (err) {
                    res.status(500).send("查询点赞数量失败");
                }
                res.status(200).send(result);
            })
        });
    });
});

module.exports = router;
